import os
import sqlite3

class simpleToolSql():
    """
    simpleToolSql for sqlite3
    简单数据库工具类
    """

    def __init__(self, filename="stsql"):
        """
        初始化数据库，默认文件名 stsql.db
        filename：文件名
        """
        self.filename = filename + ".db"
        self.db = self.create_dabase()
        # 创建游标
        self.cur = self.db.cursor()

    def __del__(self):
        """
        关闭数据库
        """
        self.cur.close()
        self.db.close()

    def create_dabase(self):
        """
        连接数据库, 如果数据库不存在创建数据库 
        允许跨线程操作数据库
        """
        return sqlite3.connect(self.filename, check_same_thread=False)

    def execute(self,sql, param=None):
        """
        执行数据库的增、删、改
        sql：sql语句
        param：数据，可以是list或tuple，亦可是None
        retutn：成功返回True
        """
        try:
            if param is None:
                self.cur.execute(sql)
            else:
                if type(param) is list:
                    self.cur.executemany(sql, param)
                else :
                    self.cur.execute(sql, param)
            count = self.db.total_changes
            self.db.commit()
        except Exception as e:
            print(e)
            return False,e
        if count > 0 :
            return True
        else :
            return False

    def get_all_table_name(self):
        """
        sqlite_master:为系统表
        系统表结构
        sqlite_master(
            type TEXT,      #类型:table-表,index-索引,view-视图
            name TEXT,      #名称:表名,索引名,视图名
            tbl_name TEXT,
            rootpage INTEGER,
            sql TEXT
            )
        """
        self.cur.execute(f"select name from sqlite_master where type='table'")
        table_names = self.cur.fetchall()
        return table_names

    def query(self,sql, param=None, get_one=False):
        """
        查询语句
        sql：sql语句
        param：参数,可为None
        retutn：成功返回True
        """
        if param is None:
            self.cur.execute(sql)
        else:
            self.cur.execute(sql,param)
        if get_one:
            return self.cur.fetchone()
        return self.cur.fetchall()

        
    def set(self,table,field=" * ",where="",isWhere=False):
        self.table = table
        self.filed = field
        if where != "" :
            self.where = where
            self.isWhere = True
        return True

if __name__ == "__main__":
    """
    测试代码
    """
    sql = simpleToolSql("test_db")
    # 创建表
    # f = sql.execute("create table test (id int not null,name text not null,age int);")
    sql.execute("create table koukou (id integer primary key,user_id text not null,pwd text);")
    
    # 插入值
    sql.execute("insert into koukou (user_id,pwd) values (?,?);",[
        ('abc','1111115'),('bca','16'),
        ('koujw0106','koujw0102'),('koujw0102','16'),
    ])
    # sql.execute("insert into test (id,name) values (?,?);",(3,'bac'))
    
    #查询值
    # res = sql.query("select * from test;")
    # print(res)
    
    # command = "update koukou set name = age"
    # sql.execute(command) 123
